﻿using AutoMapper;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Presentation;
using DocumentFormat.OpenXml.Spreadsheet;
using Elasticsearch.Net;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Core.Models;
using Magicodes.ExporterAndImporter.Excel;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MT.Enterprise.Utils.Extensions;
using NP.BPMReportPlatform.AuthorizationUserManager.Dtos;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business.Reports;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.HR.Dto;
using NP.BPMReportPlatform.RecurringJobs.Dto;
using NP.BPMReportPlatform.Reports.OrderMaterialDetail.Dtos;
using NP.BPMReportPlatform.Utils;
using NPOI.HSSF.Util;
using NPOI.OpenXmlFormats.Vml;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml.DataValidation;
using Serilog;
using SkiaSharp;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Volo.Abp.ObjectMapping;

namespace NP.BPMReportPlatform.Reports.OrderMaterialDetail
{
    public class OrderMaterialDetailService : BPMReportPlatformAppService, IOrderMaterialDetailService
    {
        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        private readonly SqlSugarClient _dbEngine;
        private readonly ILogger _logger;
        private readonly IExcelImporter _importer;
        public OrderMaterialDetailService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp, ILogger logger, IExcelImporter importer)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
            _logger = logger;
            _importer = importer;
        }

        /// <summary>
        /// 分页获取订单明细
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<OrderMaterialDetailDto>> GetList(OrderMaterialDetailInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var userList = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            //返回结果
            return new PagedResultDto<OrderMaterialDetailDto>()
            {
                TotalCount = totalCount,
                Items = userList
            };
        }

        /// <summary>
        /// 导出订单明细
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<OrderMaterialDetailDto>> ExportExcel(OrderMaterialDetailInput input)
        {
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();
            return listViewDto;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<OrderMaterialDetailDto> GetQueryable(OrderMaterialDetailInput input)
        {
            var query = _db.Queryable<OrderMaterialDetailEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.DeliveryCode), x => x.DeliveryCode.Contains(input.DeliveryCode))
                .WhereIF(!string.IsNullOrEmpty(input.Material), x => x.MaterialCode.Contains(input.Material) ||
                    x.MaterialName.Contains(input.Material))
                .Select(x => new OrderMaterialDetailDto
                {
                    OrderStatus = x.OrderStatus,
                    DeliveryCode = x.DeliveryCode,
                    SalesDoc = x.SalesDoc,
                    MaterialCode = x.MaterialCode,
                    MaterialName = x.MaterialName,
                    MaterialSpec = x.MaterialSpec,
                    MarketCategory = x.MarketCategory,
                    SubBrandDes = x.SubBrandDes,
                    ProductDivideDesc = x.ProductDivideDesc,
                    SalesUnit = x.SalesUnit,
                    BaseUnit = x.BaseUnit,
                    Unit = x.Unit,
                    Factory = x.Factory,
                    CustomerCode = x.CustomerCode,
                    CustomerName = x.CustomerName,
                    T09 = x.T09,
                    CustomerGroupDesc1 = x.CustomerGroupDesc1,
                    CustomerGroupDesc2 = x.CustomerGroupDesc2,
                    ChannelName = x.ChannelName,
                    SalesCompany = x.SalesCompany,
                    SalesRegion = x.SalesRegion,
                    SalesDeptDesc = x.SalesDeptDesc,
                    CreditStatus = x.CreditStatus,
                    CreditPassDate = x.CreditPassDate,
                    SalesDocCreateDate = x.SalesDocCreateDate,
                    DeliveryReqDate = x.DeliveryReqDate,
                    DeliveryNeedDate = x.DeliveryNeedDate,
                    DeliveryCodeCreateDate = x.DeliveryCodeCreateDate,
                    WayBillCreateDate = x.WayBillCreateDate,
                    DeliveryActualDate = x.DeliveryActualDate,
                    TransportType = x.TransportType,
                    Price = x.Price,
                    Weight = x.Weight,
                    Quantity = x.Quantity,
                    QuantityBranch = x.QuantityBranch,
                    NetWeight = x.NetWeight,
                    GrossWeight = x.GrossWeight,
                    GrossWeightTon = x.GrossWeightTon,
                    OnSaleStatus = x.OnSaleStatus,
                    IsExternalPurchase = x.IsExternalPurchase,
                });
            return query;
        }

        /// <summary>
        /// 导入订单明细
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<ImportResultExt<OrderMaterialDetailDto>> ImportExcel(List<OrderMaterialDetailDto> data)
        {
            var rst = new ImportResultExt<OrderMaterialDetailDto>
            {
                Data = data
            };

            var dataList = ObjectMapper.Map<List<OrderMaterialDetailDto>, List<OrderMaterialDetailEntity>>(data);
            // 只导入销售凭证 17或58 开头的数据
            dataList = dataList.Where(x => x.SalesDoc.StartsWith("17") || x.SalesDoc.StartsWith("58")).ToList();
            var user = await _dbPlatform.Queryable<Entities.BPM_Platform.Users>()
                .FirstAsync(x => x.WorkNumber == CurrentUser.UserName);
            var configs = await _db.Queryable<OrderMaterialConfigEntity>().ToListAsync();
            var index = 2;
            foreach (var model in dataList)
            {
                model.AddUserId = user?.UserId;
                model.AddUserName = user?.UserName;
                // 单位
                if (string.IsNullOrWhiteSpace(model.MaterialSpec))
                {
                    rst.Exception = new Exception($"第{index}行，没有物料规格");
                    break;
                }
                if (!model.MaterialSpec.Contains(",") && !model.MaterialSpec.Contains("，"))
                {
                    model.Unit = model.MaterialSpec.Trim().Take(2).JoinAsString("");
                }
                else
                {
                    model.BaseUnit = string.IsNullOrWhiteSpace(model.BaseUnit) ? "" : model.BaseUnit.Trim();
                    model.SalesUnit = string.IsNullOrWhiteSpace(model.SalesUnit) ? "" : model.SalesUnit.Trim();
                    if (model.BaseUnit == model.SalesUnit)
                    {
                        model.Unit = model.MaterialSpec.Take(2).JoinAsString("");
                    }
                    else
                    {
                        var specArr = model.MaterialSpec.Split(new char[] { ',', '，' }, StringSplitOptions.RemoveEmptyEntries);
                        if (specArr.Length > 1)
                        {
                            model.Unit = specArr[1].Trim().Take(2).JoinAsString("");
                        }
                    }
                }
                //数量-支
                if (string.IsNullOrWhiteSpace(model.Unit))
                {
                    rst.Exception = new Exception($"第{index}行，取不到单位");
                    break;
                }
                if (!model.Quantity.HasValue)
                {
                    rst.Exception = new Exception($"第{index}行，没有数量");
                    break;
                }
                if (model.BaseUnit == model.SalesUnit)
                {
                    model.QuantityBranch = model.Quantity;
                }
                else
                {
                    if (decimal.TryParse(model.Unit, out var unitNumber))
                    {
                        model.QuantityBranch = model.Quantity * unitNumber;
                    }
                    else
                    {
                        rst.Exception = new Exception($"第{index}行，单位【{model.Unit}】无法转为数值");
                        break;
                    }
                }
                //毛重 在售状态 是否外购
                if (string.IsNullOrWhiteSpace(model.MaterialCode))
                {
                    rst.Exception = new Exception($"第{index}行，没有物料");
                    break;
                }
                var config = configs.FirstOrDefault(x => x.MaterialCode == model.MaterialCode || x.NewMaterialCode == model.MaterialCode);
                if (config == null)
                {
                    //rst.Exception = new Exception($"第{index}行，物料编号【{model.MaterialCode}】未找到物料基础数据");
                    //break;
                }
                model.GrossWeight = config?.GrossWeight ?? 0;
                model.OnSaleStatus = config?.OnSaleStatus;
                model.IsExternalPurchase = config?.IsExternalPurchase;
                // 毛重-支
                model.GrossWeightTon = model.QuantityBranch.HasValue && model.GrossWeight.HasValue ?
                    Math.Round(model.QuantityBranch.Value * model.GrossWeight.Value / 1000, 6) : 0;

                index++;
            }

            if (!rst.HasError)
            {
                try
                {
                    _db.BeginTran();

                    await _db.Deleteable<OrderMaterialDetailEntity>().ExecuteCommandAsync();

                    await _db.Insertable(dataList).ExecuteCommandAsync();

                    _db.CommitTran();
                }
                catch (Exception ex)
                {
                    rst.Exception = new Exception(ex.Message);
                    _db.RollbackTran();
                }
            }

            var resp = new ImportResultExt<OrderMaterialDetailDto>()
            {
                Exception = rst.Exception,
                RowErrors = rst.RowErrors,
                TemplateErrors = rst.TemplateErrors
            };
            return resp;
        }

        /// <summary>
        /// 导出订单明细导入模板
        /// </summary>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<FileStream> ExportTemplate()
        {
            var fileName = Environment.CurrentDirectory + "/wwwroot/excel/订单明细导入模板.xlsx";
            if (File.Exists(fileName))
            {
                return File.OpenRead(fileName);
            }
            else
            {
                throw new Exception("文件未找到");
            }
        }

        /// <summary>
        /// 导出吨数不足吨位的数据
        /// </summary>
        /// <returns></returns>
        public async Task<MemoryStream> ExportTonUnEnoughData()
        {
            var fileName = Environment.CurrentDirectory + "/wwwroot/excel/订单明细吨位不足导出模板.xlsx";

            if (!File.Exists(fileName))
            {
                throw new Exception("导出模板未找到");
            }
            var fs = File.OpenRead(fileName);
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);

            var dataList = await _db.Queryable<OrderMaterialDetailEntity>()
                .Where(x => x.OrderStatus == "未过账" || x.OrderStatus == "未清").ToListAsync();
            var unEnoughData = new List<OrderDetailTonUnEnoughDto>();

            var font = workbook.CreateFont();
            font.FontHeightInPoints = 9;
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.SetFont(font);

            // 剔除湘鄂，安徽，江西的数据
            dataList = dataList.Where(x => !x.SalesCompany.Contains("湘鄂") && !x.SalesCompany.Contains("安徽") && !x.SalesRegion.Contains("江西")).ToList();

            var groupDataList = dataList.GroupBy(x => new { x.SalesRegion, x.SalesCompany, x.CustomerCode }).ToList();
            var index = 2;
            foreach (var groupData in groupDataList)
            {
                if (!groupData.Any())
                {
                    continue;
                }

                var data = groupData.First();
                var dto = new OrderDetailTonUnEnoughDto
                {
                    SalesRegion = data.SalesRegion,
                    SalesCompany = data.SalesCompany,
                    CustomerCode = data.CustomerCode,
                    CustomerName = data.CustomerName,
                    UnPostWeightTon = groupData.Where(x => x.OrderStatus == "未过账" && x.GrossWeightTon.HasValue).Sum(x => x.GrossWeightTon),
                    OutstandWeightTon = groupData.Where(x => x.OrderStatus == "未清" && x.GrossWeightTon.HasValue).Sum(x => x.GrossWeightTon)
                };
                dto.GrossWeightTon = dto.UnPostWeightTon + dto.OutstandWeightTon;
                dto.Remark = "小于0.5T";
                if (dto.GrossWeightTon < (decimal)0.5 && dto.GrossWeightTon > 0)
                {
                    unEnoughData.Add(dto);
                    var columns = new List<string>
                    {
                        dto.SalesCompany,
                        dto.SalesRegion,
                        dto.CustomerCode,
                        dto.CustomerName,
                        dto.UnPostWeightTon?.ToString(),
                        dto.OutstandWeightTon?.ToString(),
                        dto.GrossWeightTon?.ToString(),
                        dto.Remark
                    };
                    IRow row = sheet.GetRow(index) ?? sheet.CreateRow(index);
                    for (int i = 0; i < columns.Count; i++)
                    {
                        ICell cell = row.GetCell(i) ?? row.CreateCell(i);
                        cell.SetCellValue(columns[i]);
                        cell.CellStyle = cellStyle;
                    }
                    index++;
                }
            }

            var unEnoughSummaryData = new List<UnEnoughDataSummaryDto>();
            var companys = await _dbPlatform.Queryable<Dictionaries>().Where(x => x.TypeCode == "SalesCompany").ToListAsync();
            companys.ForEach(x =>
            {
                var valueArr = x.Value.Split(",");
                unEnoughSummaryData.Add(new UnEnoughDataSummaryDto
                {
                    SalesRegion = x.Name,
                    ResponsiblePerson1 = valueArr.Length > 0 ? valueArr[0] : "",
                    ResponsiblePerson2 = valueArr.Length > 1 ? valueArr[1] : "",
                });
            });
            var groupUnEnoughData = unEnoughData.GroupBy(x => x.SalesCompany).ToList();
            var rowIndex = 3;
            var colIndex = 9;
            ICellStyle cellStyle1 = workbook.CreateCellStyle();
            cellStyle1.BorderLeft = BorderStyle.Thin;
            cellStyle1.BorderRight = BorderStyle.Thin;
            cellStyle1.BorderTop = BorderStyle.Thin;
            cellStyle1.BorderBottom = BorderStyle.Thin;
            cellStyle1.Alignment = HorizontalAlignment.Center;
            cellStyle1.SetFont(font);
            ICellStyle cellStyle2 = workbook.CreateCellStyle();
            cellStyle2.CloneStyleFrom(cellStyle1);
            cellStyle2.FillForegroundColor = HSSFColor.Yellow.Index;
            cellStyle2.FillPattern = FillPattern.SolidForeground;
            foreach (var data in groupUnEnoughData)
            {
                if (!data.Any() || string.IsNullOrEmpty(data.First().SalesCompany))
                {
                    continue;
                }

                var dto = unEnoughSummaryData.FirstOrDefault(x => x.SalesRegion == data.First().SalesCompany);
                if (dto != null)
                {
                    dto.UnEnoughCustomerCount = data.Count();
                }
                else
                {
                    dto = new UnEnoughDataSummaryDto
                    {
                        SalesRegion = data.First().SalesCompany,
                        UnEnoughCustomerCount = data.Count(),
                    };
                    unEnoughSummaryData.Add(dto);
                }
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                (row.GetCell(colIndex) ?? row.CreateCell(colIndex)).SetCellValue(dto.SalesRegion);
                (row.GetCell(colIndex + 1) ?? row.CreateCell(colIndex + 1)).SetCellValue(dto.UnEnoughCustomerCount);
                (row.GetCell(colIndex + 2) ?? row.CreateCell(colIndex + 2)).SetCellValue(dto.ResponsiblePerson1);
                (row.GetCell(colIndex + 3) ?? row.CreateCell(colIndex + 3)).SetCellValue(dto.ResponsiblePerson2);
                for(var i = 0; i < 4; i++)
                {
                    (row.GetCell(colIndex + i) ?? row.CreateCell(colIndex + i)).CellStyle = cellStyle1;
                }

                rowIndex++;
            }

            var sumRow = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
            (sumRow.GetCell(colIndex) ?? sumRow.CreateCell(colIndex)).SetCellValue("合计");
            (sumRow.GetCell(colIndex + 1) ?? sumRow.CreateCell(colIndex + 1)).SetCellValue(unEnoughSummaryData.Sum(x => x.UnEnoughCustomerCount));
            for (var i = 0; i < 4; i++)
            {
                if (i == 0 || i == 1)
                {
                    (sumRow.GetCell(colIndex + i) ?? sumRow.CreateCell(colIndex + i)).CellStyle = cellStyle2;
                }
                else
                {
                    (sumRow.GetCell(colIndex + i) ?? sumRow.CreateCell(colIndex + i)).CellStyle = cellStyle1;
                }
            }

            var memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        /// <summary>
        /// 导入成品计划报表
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<ImportResultExt<EndProductPlanDto>> ImportEndProductPlanData(List<EndProductPlanDto> data)
        {
            var rst = new ImportResultExt<EndProductPlanDto>
            {
                Data = data
            };

            var dataList = ObjectMapper.Map<List<EndProductPlanDto>, List<OrderMaterialEndProductPlan>>(data);
            var availableInventoryData = await _db.Queryable<OrderMaterialAvailableInventory>().ToListAsync();
            foreach (var item in dataList)
            {
                item.DeliverableInventory = item.CurrentInventory - item.DeliveryFinish -
                    availableInventoryData
                    .Where(x => x.MaterialCode == item.MaterialCode && x.AvailableInventory != null)
                    .Sum(x => x.AvailableInventory);
            }

            try
            {
                _db.BeginTran();

                await _db.Deleteable<OrderMaterialEndProductPlan>().ExecuteCommandAsync();

                await _db.Insertable(dataList).ExecuteCommandAsync();

                _db.CommitTran();
            }
            catch (Exception ex)
            {
                rst.Exception = new Exception(ex.Message);
                _db.RollbackTran();
            }

            var resp = new ImportResultExt<EndProductPlanDto>()
            {
                Exception = rst.Exception,
                RowErrors = rst.RowErrors,
                TemplateErrors = rst.TemplateErrors
            };
            return resp;
        }

        /// <summary>
        /// 导入物料基础配置
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<ImportResultExt<OrderMaterialConfigDto>> ImportConfig(List<OrderMaterialConfigDto> data)
        {
            var rst = new ImportResultExt<OrderMaterialConfigDto>
            {
                Data = data
            };

            var dataList = ObjectMapper.Map<List<OrderMaterialConfigDto>, List<OrderMaterialConfigEntity>>(data);

            try
            {
                _db.BeginTran();

                await _db.Deleteable<OrderMaterialConfigEntity>().ExecuteCommandAsync();

                await _db.Insertable(dataList).ExecuteCommandAsync();

                _db.CommitTran();
            }
            catch (Exception ex)
            {
                rst.Exception = new Exception(ex.Message);
                _db.RollbackTran();
            }

            var resp = new ImportResultExt<OrderMaterialConfigDto>()
            {
                Exception = rst.Exception,
                RowErrors = rst.RowErrors,
                TemplateErrors = rst.TemplateErrors
            };
            return resp;
        }

        /// <summary>
        /// 导出未清分析汇总数据
        /// </summary>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<MemoryStream> ExportUnclearSummaryData()
        {
            var fileName = Environment.CurrentDirectory + "/wwwroot/excel/未清分析汇总导出模板.xlsx";

            if (!File.Exists(fileName))
            {
                throw new Exception("导出模板未找到");
            }
            var fs = File.OpenRead(fileName);
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);

            var dataList = await _db.Queryable<OrderMaterialDetailEntity>()
                .Where(x => x.OrderStatus == "未清").ToListAsync();
            var unclearSummaryData = new List<UnclearSummaryDto>();

            var font = workbook.CreateFont();
            font.FontHeightInPoints = 9;
            font.FontName = "等线";
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.SetFont(font);

            var cycleConfigs = await _dbPlatform.Queryable<Dictionaries>().Where(x => x.TypeCode == "DelieveryCycle").ToListAsync();
            var endProductPlans = await _db.Queryable<OrderMaterialEndProductPlan>().ToListAsync();
            var index = 1;
            foreach (var data in dataList)
            {
                var model = new UnclearSummaryDto
                {
                    SalesCompany = data.SalesCompany,
                    SalesDoc = data.SalesDoc,
                    SalesDeptDesc = data.SalesDeptDesc,
                    SalesRegion = data.SalesRegion,
                    MaterialCode = data.MaterialCode,
                    MaterialName = data.MaterialName,
                    CustomerCode = data.CustomerCode,
                    CustomerName = data.CustomerName,
                    CurrentDate = DateTime.Today.ToString("yyyyMMdd"),
                    CreditStatus = data.CreditStatus,
                    IsExternalPurchase = data.IsExternalPurchase,
                    ResponsiblePerson = GetUnclearReposiblePerson(data.IsExternalPurchase),
                    QuantityBranch = data.QuantityBranch,
                    OnSaleStatus = data.OnSaleStatus,
                };

                //库存不足
                var endProductPlan = endProductPlans.FirstOrDefault(x=>x.MaterialCode == data.MaterialCode);
                if (endProductPlan != null)
                {
                    model.inventoryUnEnough = endProductPlan.DeliverableInventory >= 0 ? "库存满足" : "库存不满足";
                }

                //应交单日期
                var cycleConfig = cycleConfigs.FirstOrDefault(x => x.Name == model.SalesRegion);
                if (cycleConfig != null && int.TryParse(cycleConfig.Value, out int cycleInterval))
                {
                    if (model.SalesCompany.Contains("湘鄂") || model.SalesCompany.Contains("安徽") || model.SalesRegion.Contains("江西"))
                    {
                        if (!string.IsNullOrEmpty(data.DeliveryNeedDate) && DateTime.TryParseExact(data.DeliveryNeedDate.Trim(), "yyyyMMdd", null, DateTimeStyles.None, out DateTime deliveryNeedDate))
                        {
                            model.DueDate = deliveryNeedDate.AddDays(7 - cycleInterval).ToString("yyyMMdd");
                        }
                        else
                        {
                            model.DueDate = DateTime.Today.AddDays(7 - cycleInterval).ToString("yyyMMdd");
                        }
                    }
                    else
                    {
                        if (model.CreditStatus == "通过" && DateTime.TryParseExact(data.CreditPassDate, "yyyyMMdd", null, DateTimeStyles.None, out DateTime creditPassDate))
                        {
                            model.DueDate = creditPassDate.AddDays(7 - cycleInterval).ToString("yyyMMdd");
                        }
                        else
                        {
                            model.DueDate = DateTime.Today.AddDays(7 - cycleInterval).ToString("yyyMMdd");
                        }
                    }
                }

                //超期天数，超期区间，是否超期
                if (!string.IsNullOrEmpty(model.DueDate) && DateTime.TryParseExact(model.DueDate, "yyyyMMdd", null, DateTimeStyles.None, out DateTime dueDate))
                {
                    var overDueDays = DateTime.Today.Subtract(dueDate).Days;
                    model.IsOverDue = overDueDays >= 0 ? "未超期" : "超期";
                    if (overDueDays < 0)
                    {
                        if (overDueDays >= -7 && overDueDays < 0)
                        {
                            model.OverDueInterval = "0-7天";
                        }
                        else if (overDueDays >= -15 && overDueDays < -7)
                        {
                            model.OverDueInterval = "7-15天";
                        }
                        else if (overDueDays < -15)
                        {
                            model.OverDueInterval = "15天以上";
                        }
                    }
                    else
                    {
                        model.OverDueInterval = "未超期";
                    }
                    model.OverDueDays = (overDueDays * -1).ToString();
                }

                unclearSummaryData.Add(model);

                var columns = new List<string>
                    {
                        model.SalesCompany,
                        model.SalesDoc,
                        model.SalesDeptDesc,
                        model.SalesRegion,
                        model.MaterialCode,
                        model.MaterialName,
                        model.CustomerCode,
                        model.CustomerName,
                        model.inventoryUnEnough,
                        model.CurrentDate,
                        model.DueDate,
                        model.OverDueInterval,
                        model.OverDueDays,
                        model.IsOverDue,
                        model.CreditStatus,
                        model.IsExternalPurchase,
                        model.ResponsiblePerson,
                        model.QuantityBranch?.ToString() ?? "",
                        model.SatisfyDate,
                        model.NotSatisfyReason,
                        model.OnSaleStatus,
                        model.Remark,
                    };
                IRow row = sheet.GetRow(index) ?? sheet.CreateRow(index);
                for (int i = 0; i < columns.Count; i++)
                {
                    ICell cell = row.GetCell(i) ?? row.CreateCell(i);
                    cell.SetCellValue(columns[i]);
                    cell.CellStyle = cellStyle;
                }

                index++;
            }

            var memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        private string GetUnclearReposiblePerson(string produceType)
        {
            if(produceType == "自产")
            {
                return "邹佳新";
            }
            else if(produceType == "外购")
            {
                return "庞忐";
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 导入可用库存
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<ImportResultExt<AvailableInventoryDto>> ImportAvailableInventoryData(List<AvailableInventoryDto> data)
        {
            var rst = new ImportResultExt<AvailableInventoryDto>
            {
                Data = data
            };

            var dataList = ObjectMapper.Map<List<AvailableInventoryDto>, List<OrderMaterialAvailableInventory>>(data);

            try
            {
                _db.BeginTran();

                await _db.Deleteable<OrderMaterialAvailableInventory>().ExecuteCommandAsync();

                await _db.Insertable(dataList).ExecuteCommandAsync();

                _db.CommitTran();
            }
            catch (Exception ex)
            {
                rst.Exception = new Exception(ex.Message);
                _db.RollbackTran();
            }

            var resp = new ImportResultExt<AvailableInventoryDto>()
            {
                Exception = rst.Exception,
                RowErrors = rst.RowErrors,
                TemplateErrors = rst.TemplateErrors
            };
            return resp;
        }
    }
}
